
import json
import requests


    
def parse_flight(url, raw_data):
    
    json_data= json.loads(raw_data)
    for k in json_data['flights'].keys():
        data={}
        data['date_d'] = url.split('/')[7]
        data['time_d'] = url.split('/')[8]
        
        flight_data= json_data['flights'][k]
        if flight_data.__contains__('cancelled') :
            data['cancelled']=flight_data['cancelled']
            data['status']= flight_data['flightStatus']
            data['airport_s3']= flight_data['origin']['iata']
            data['airport_d3']= flight_data['destination']['iata']
            
            data['takeoff_s']= flight_data['takeoffTimes']['scheduled']
            data['takeoff_e']= flight_data['takeoffTimes']['estimated']
            data['takeoff_a']= flight_data['takeoffTimes']['actual']
            #print(data)
            if data['takeoff_a'] !=None:
                data['delay'] = data['takeoff_a'] - data['takeoff_s']
            else:
                data['delay'] = 0
            data['landing_s']= flight_data['landingTimes']['scheduled']
            data['landing_e']= flight_data['landingTimes']['estimated']
            data['landing_a']= flight_data['landingTimes']['actual']
            
            
            return data

import mysql.connector

def save_flight(flight_id, data):
    
    cnx = mysql.connector.connect(user='flightdata', password='flightdata',
                                  host='127.0.0.1',
                                  database='flightdata')
    cursor = cnx.cursor()
    
    update_flight = "update  flight \
                  set   cancelled=%s, status=%s, \
                  date_d=%s, time_d=%s, airport_s3=%s, airport_d3=%s, \
                  takeoff_s=%s, takeoff_e=%s,takeoff_a=%s, delay=%s,\
                    landing_s=%s, landing_e=%s,landing_a=%s \
                where id=%s"
   
    cursor.execute(update_flight, (data['cancelled'], data['status'],
                                   data['date_d'], data['time_d'],data['airport_s3'], data['airport_d3'], 
                                   data['takeoff_s'], data['takeoff_e'], data['takeoff_a'], data['delay'],
                                   data['landing_s'], data['landing_e'], data['landing_a'],flight_id))

    cnx.commit()

    cursor.close()
    cnx.close()

    

if __name__ == "__main__":

    cnx = mysql.connector.connect(user='flightdata', password='flightdata',
                                  host='127.0.0.1',
                                  database='flightdata')
    cursor = cnx.cursor()
    #cursor.execute("select id,url from flight where date_d is  null  limit 10 ")
    cursor.execute("select id, url, raw_data from flight where date_d is  null and raw_data is not null ")


    for (id, url, raw_data) in cursor:
        print (id,url)
        url= 'https://zh.flightaware.com' +url
        try:
            data= parse_flight(url, raw_data)
            if data!=None:
                print(data)
                save_flight(id, data)
        except  Exception as e:
            print('Error: '+ str(e))
            raise